OpenEdge Data Management:<br />SQL Reference Column constraints

Release 10.1A: OpenEdge Data Management:
SQL Reference

Column constraints

Specifies a constraint for a column that restricts the values that the column can store. INSERT, UPDATE, or DELETE statements that violate the constraint fail. The database returns a constraint violation error with an SQLCODE of -20116.

Column constraints are similar to table constraints, but their definitions are associated with a single column rather than the entire table.

Syntax
CONSTRAINT constraint_name 
   NOT NULL [ PRIMARY KEY | UNIQUE ] 
  | REFERENCES [ owner_name.]table_name [ ( column_name ) ] 
  | CHECK ( search_condition ) 
CONSTRAINT constraint_name
Allows you to assign a name that you choose to the column constraint. While this specification is optional, this facilitates making changes to the column definition. If you do not specify a constraint_name, the database assigns a name. These names can be long and unwieldy, and you must query system tables to retrieve the name.
NOT NULL
Restricts values in the column to values that are not null.
NOT NULL PRIMARY KEY
Defines the column as the primary key for the table. There can be at most one primary key for a table. A column with the NOT NULL PRIMARY KEY constraint should not contain null or duplicate values.

Other tables can name primary keys as foreign keys in their REFERENCES clauses. If they do, SQL restricts operations on the table containing the primary key in the following ways:

DROP TABLE statements that delete the table fail.

DELETE and UPDATE statements that modify values in the column that match a foreign key's value also fail.

NOT NULL UNIQUE
Defines the column as a unique key that cannot contain null or duplicate values. Columns with NOT NULL UNIQUE constraints defined for them are also called candidate keys.

Other tables can name unique keys in their REFERENCES clauses. If they do, SQL restricts operations on the table containing the unique key.
REFERENCES table_name [ ( column_name ) ]
Defines the column as a foreign key and specifies a matching primary or unique key in another table. The REFERENCES clause names the matching primary or unique key.

A foreign key and its matching primary or unique key specify a referential constraint. A value stored in the foreign key must either be null or be equal to some value in the matching unique or primary key.

You can omit the column_name argument if the table specified in the REFERENCES clause has a primary key and you want the primary key to be the matching key for the constraint.
CHECK ( search_condition )
Specifies a column-level check constraint. SQL restricts the form of the search condition. The search condition must not:

Refer to any column other than the one with which it is defined.

Contain aggregate functions, subqueries, or parameter references.

Examples

The following example shows the creation of a primary key column on the supplier table:
CREATE TABLE supplier ( 
     supp_no     INTEGER CONSTRAINT supp_key_con NOT NULL PRIMARY KEY, 
     name        CHAR (30),  
     status      SMALLINT, 
     city        CHAR (20)  
) ; 
The following example creates a NOT NULL UNIQUE constraint to define the column ss_no as a unique key for the employee table:
CREATE TABLE employee ( 
     empno        INTEGER NOT NULL PRIMARY KEY, 
     ss_no        INTEGER NOT NULL UNIQUE, 
     ename        CHAR (19), 
     sal          NUMERIC (10, 2), 
     deptno       INTEGER NOT NULL 
) ;  
The following example defines order_item.orditem_order_no as a foreign key that references the primary key orders.order_no:
CREATE TABLE orders ( 
     order_no INTEGER NOT NULL PRIMARY KEY, 
     order_date DATE 
) ; 
  
CREATE TABLE order_item ( 
     orditem_order_no INTEGER REFERENCES orders ( order_no ), 
     orditem_quantity INTEGER 
) ;  
Note: The second CREATE TABLE statement in the previous example could have omitted the column name order_no in the REFERENCES clause, since it refers to the primary key of table orders.

The following example creates a check constraint:
CREATE TABLE supplier ( 
     supp_no     INTEGER NOT NULL, 
     name        CHAR (30),  
     status      SMALLINT, 
     city        CHAR (20) CHECK (supplier.city <> 'BadApple') 
) ; 
Note: If a column is defined with a UNIQUE column constraints, no error results if more than one row has a NULL value for the column.